﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Common;
using System.Collections;
using System.Collections.Specialized;
using Dal.Properties;
using System.Configuration;

namespace Dal
{
    public class DatabaseAbstractionLayer
    {
        String connectionString;

        public DatabaseAbstractionLayer(String ConnectionString)
        {
            connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            if (connectionString == null)
                connectionString = Settings1.Default.ConnectionString;
        }

        public long InsertDocument(Document Document) //gets unique Document ID
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("insert_document",con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@doc_title", Document.Fields[(int)Document.Section.Title].Text));
                command.Parameters.Add(new SqlParameter("@doc_summary", Document.Fields[(int)Document.Section.Summary].Text));
                command.Parameters.Add(new SqlParameter("@doc_text", Document.Fields[(int)Document.Section.Text].Text));
                command.Parameters.Add(new SqlParameter("@doc_date", Document.DateTime));
                command.Parameters.Add(new SqlParameter("@doc_author", Document.Fields[(int)Document.Section.Author].Text));
                command.Parameters.Add(new SqlParameter("@doc_url", Document.Url.ToString()));
                command.Parameters.Add(new SqlParameter("@doc_comment_num", Document.CommentNumber));
                command.Parameters.Add(new SqlParameter("@doc_tb_num", Document.talkbackNumber));
                command.Parameters.Add(new SqlParameter("@doc_type", Document.DocType));
                command.Parameters.Add(new SqlParameter("@doc_priority", Document.SitePriority));
                command.Parameters.Add(new SqlParameter("@doc_id", System.Data.SqlDbType.BigInt));
                command.Parameters.Add(new SqlParameter("@site_name", Document.SiteName));
                command.Parameters["@doc_id"].Direction = System.Data.ParameterDirection.ReturnValue;
                command.ExecuteNonQuery();
                return long.Parse(command.Parameters["@doc_id"].Value.ToString());
            }
        }

        /// <summary>
        /// Gets document by Doc_id. 
        /// </summary>
        /// <param name="DocumentID"></param>
        /// <returns>Document object. NULL if document with specified ID not found</returns>
        public Document GetDocumentByID(long DocumentID)
        {

           Document Document = null;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                
                con.Open();
                SqlCommand command = new SqlCommand("get_document_by_id", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@doc_id", DocumentID));
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
					int priority, comment, type, tb;
					DateTime date;
					Uri uri;
                    string siteName;

					if (!int.TryParse(reader["doc_priority"].ToString(), out priority)) priority = 0;
					if (!DateTime.TryParse(reader["doc_date"].ToString(), out date)) date = new DateTime();
					if (!int.TryParse(reader["doc_comment_num"].ToString(), out comment)) comment = 0;
                    if (!int.TryParse(reader["doc_tb_num"].ToString(), out tb)) tb = 0;
					if (!int.TryParse(reader["dt_id"].ToString(), out type)) type =0;
					if (!Uri.TryCreate(reader["doc_url"].ToString(), UriKind.RelativeOrAbsolute, out uri)) 
						uri=new Uri("http://something.com");// TODO: ?!
                    siteName = reader["site_name"].ToString();
                    Document = new Document(DocumentID, date, uri, comment, tb, (Document.Type)type, priority, siteName);

                    Document.Fields[(int)Document.Section.Title] = new Field(reader["doc_title"].ToString(), Document.Section.Title);
                    Document.Fields[(int)Document.Section.Summary] = new Field(reader["doc_summary"].ToString(), Document.Section.Summary);
                    Document.Fields[(int)Document.Section.Text] = new Field(reader["doc_text"].ToString(), Document.Section.Text);
                    Document.Fields[(int)Document.Section.Author] = new Field(reader["doc_author"].ToString(), Document.Section.Author);
                  
                }
            }
            return Document;
        }


        public SiteNames GetSiteNames(string siteName)
        {
            SiteNames siteNames = new SiteNames();
            using (SqlConnection con = new SqlConnection(connectionString))
            {

                con.Open();
                SqlCommand command = new SqlCommand("get_site_names", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@name", siteName));
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    siteNames.BlogName = reader["blog_name"].ToString();
                    siteNames.ForumName = reader["forum_name"].ToString();
                    siteNames.TalkbackName = reader["talkback_name"].ToString();
                    siteNames.StoryName = reader["story_name"].ToString();
                    siteNames.DisplayName = reader["display_name"].ToString();
                    siteNames.SiteName = siteName;
                   
                }
                return siteNames;
            }
              
               
        }
    

        public Document GetDocumentByUrlCommentNumber(Uri Url, long CommentNumber)
        {

            Document Document = null;
            using (SqlConnection con = new SqlConnection(connectionString))
            {

                con.Open();
                SqlCommand command = new SqlCommand("get_document_by_url_comment_num", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@doc_url", Url.ToString()));
                command.Parameters.Add(new SqlParameter("@doc_comment_num", CommentNumber.ToString()));
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Document = new Document(Int32.Parse(reader["doc_id"].ToString()), DateTime.Parse(reader["doc_date"].ToString()),
                        new Uri(reader["doc_url"].ToString()), 
                        long.Parse(reader["doc_comment_num"].ToString()),
                        Int32.Parse(reader["doc_tb_num"].ToString()), 
                        (Document.Type)Int32.Parse(reader["dt_id"].ToString()), 
                        Int32.Parse(reader["doc_priority"].ToString()),
                        reader["site_name"].ToString());

                    Document.Fields[(int)Document.Section.Title] = new Field(reader["doc_title"].ToString(), Document.Section.Title);
                    Document.Fields[(int)Document.Section.Summary] = new Field(reader["doc_summary"].ToString(), Document.Section.Summary);
                    Document.Fields[(int)Document.Section.Text] = new Field(reader["doc_text"].ToString(), Document.Section.Text);
                    Document.Fields[(int)Document.Section.Author] = new Field(reader["doc_author"].ToString(), Document.Section.Author);

                }
            }
            return Document;
        }

        public void AddUserTerm(string Term, string UserName)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {

                con.Open();
                SqlCommand command = new SqlCommand("add_user_term", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@term_text", Term));
                command.Parameters.Add(new SqlParameter("@user_name", UserName));
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Index a word in a document at a certain position. If 
        /// </summary>
        /// <param name="Word"></param>
        /// <param name="Document"></param>
        /// <param name="WordPosition"></param>
        public void AddWord(Word Word, Document Document, WordPos WordPosition)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("add_word", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@word", Word.Text));
                command.Parameters.Add(new SqlParameter("@doc_url", Document.Url));
                command.Parameters.Add(new SqlParameter("@ds_id", WordPosition.Section));
                command.Parameters.Add(new SqlParameter("@position", WordPosition.Position));
                command.ExecuteNonQuery();
            }
        }

        public bool IsDocumentExist(string url, long commentNumber)
        {
            bool res = false;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("is_document_exist", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@url", url));
                command.Parameters.Add(new SqlParameter("@comment_num", commentNumber));
                command.Parameters.Add(new SqlParameter("@ret", 0));
                command.Parameters["@ret"].Direction = System.Data.ParameterDirection.ReturnValue;
                command.ExecuteNonQuery();
                if ((int)command.Parameters["@ret"].Value == 0)
                    res = false;
                if ((int)command.Parameters["@ret"].Value == 1)
                    res = true;    
            }
            return res;
        }

        public bool IsAuthenticated(string userName, string password)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("is_authenticated", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@user_name", userName));
                command.Parameters.Add(new SqlParameter("@password", password));
                command.Parameters.Add(new SqlParameter("@ret", password));
                command.Parameters["@ret"].Direction = 
                    System.Data.ParameterDirection.ReturnValue;
                command.ExecuteNonQuery();
                if ((int)command.Parameters["@ret"].Value == 1)
                    return true;
                else
                    return false;
            }
            return false;

        }

        public User GetUserByUserName(string UserName)
        {
            User user = null;
            if (null == UserName)
                return user;
            List<string> UserQueries = new List<string>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("get_user_queries_by_user_name", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@user_name", UserName));
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    UserQueries.Add(reader["query_text"].ToString());
                }
                reader.Close();
                //Get Categories.

                List<Category> Categories = new List<Category>();

                command = new SqlCommand("get_user_category_names", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@user_name", UserName));
                reader = command.ExecuteReader();
                while (reader.Read()) //For each category 
                {
                    using (SqlConnection con2 = new SqlConnection(connectionString))
                    {
                        con2.Open();
                        Category Category = new Category();
                        Category.Name = reader["c_name"].ToString();
                        Category.UpdateRealTime = (bool)reader["update_real_time"];

                        //Get Notification times.
                        List<UpdateTime> UpdateTimes = new List<UpdateTime>();

                        SqlCommand command2 = new SqlCommand("get_user_category_notification_times", con2);
                        command2.CommandType = System.Data.CommandType.StoredProcedure;
                        command2.Parameters.Add(new SqlParameter("@user_name", UserName));
                        command2.Parameters.Add(new SqlParameter("@c_name", Category.Name));
                        SqlDataReader reader2 = command2.ExecuteReader();
                        while (reader2.Read())
                        {

                            UpdateTime ut = new UpdateTime();
                            ut.Day = (DayOfWeek)(Int32.Parse(reader2["day"].ToString()));
                            //ut.Hour = Int32.Parse(reader2["hour"].ToString());
                            ut.Hour = TimeSpan.FromHours(Double.Parse(reader2["hour"].ToString()));
                            UpdateTimes.Add(ut);
                        }
                        reader2.Close();
                        //Get Site Priorities
                        List<DomainPriority> DomainPriorities = new List<DomainPriority>();

                        command2 = new SqlCommand("get_user_category_site_priorities", con2);
                        command2.CommandType = System.Data.CommandType.StoredProcedure;
                        command2.Parameters.Add(new SqlParameter("@user_name", UserName));
                        command2.Parameters.Add(new SqlParameter("@c_name", Category.Name));
                        reader2 = command2.ExecuteReader();

                        while (reader2.Read())
                        {
                            DomainPriority dp = new DomainPriority(reader2["name"].ToString(),
                                Int32.Parse(reader2["priority"].ToString()));
                            DomainPriorities.Add(dp);
                        }
                        reader2.Close();
                        //Get Document Types 
                        List<Document.Type> DocTypes = new List<Document.Type>();

                        command2 = new SqlCommand("get_user_category_doc_types", con2);
                        command2.CommandType = System.Data.CommandType.StoredProcedure;
                        command2.Parameters.Add(new SqlParameter("@user_name", UserName));
                        command2.Parameters.Add(new SqlParameter("@c_name", Category.Name));
                        reader2 = command2.ExecuteReader();

                        while (reader2.Read())
                        {
                            DocTypes.Add((Document.Type)(Int32.Parse(reader2["dt_id"].ToString())));
                        }
                        reader2.Close();
                        //Get Category Queries
                        List<string> Queries = new List<string>();

                        command2 = new SqlCommand("get_user_category_queries", con2);
                        command2.CommandType = System.Data.CommandType.StoredProcedure;
                        command2.Parameters.Add(new SqlParameter("@user_name", UserName));
                        command2.Parameters.Add(new SqlParameter("@c_name", Category.Name));
                        reader2 = command2.ExecuteReader();

                        while (reader2.Read())
                        {
                            Queries.Add(reader2["query_text"].ToString());
                        }

                        //Got all category stuff, create category object.
                        Category.DocumentTypes = DocTypes;
                        Category.DomainPriorities = DomainPriorities;
                        Category.Queries = Queries;
                        Category.UpdateTimes = UpdateTimes;

                        reader2.Close();
                        con2.Close();
                        //Add category to user object.
                        Categories.Add(Category);
                    }
                    
                }
                reader.Close();
                //Got Categories, get user details.

                command = new SqlCommand("get_user_by_user_name", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@user_name", UserName));
                reader = command.ExecuteReader();
                while (reader.Read())
                    user = new User((long)reader["u_id"], reader["user_name"].ToString(),
                       reader["email"].ToString(), reader["first_name"].ToString(),
                       reader["last_name"].ToString(), Categories);


                return user;
            }
        }

        public void DeleteCategory(string userName, string categoryName)
        {
              SqlTransaction trans = null;

              using (SqlConnection con = new SqlConnection(connectionString))
              {
                  con.Open();
                  trans = con.BeginTransaction();
                  try
                  {
                      SqlCommand command = new SqlCommand("remove_user_category_notification_time", con, trans);
                      command.CommandType = System.Data.CommandType.StoredProcedure;
                      command.Parameters.Add(new SqlParameter("@user_name", userName));
                      command.Parameters.Add(new SqlParameter("@c_name", categoryName));
                      command.ExecuteNonQuery();

                      command = new SqlCommand("remove_user_category_notify_doc_type", con, trans);
                      command.CommandType = System.Data.CommandType.StoredProcedure;
                      command.Parameters.Add(new SqlParameter("@user_name", userName));
                      command.Parameters.Add(new SqlParameter("@c_name", categoryName));
                      command.ExecuteNonQuery();

                      //Remove existing priorities from DB for user + category
                      command = new SqlCommand("remove_user_category_priority", con, trans);
                      command.CommandType = System.Data.CommandType.StoredProcedure;
                      command.Parameters.Add(new SqlParameter("@user_name", userName));
                      command.Parameters.Add(new SqlParameter("@c_name", categoryName));
                      command.ExecuteNonQuery();

                      //Remove existing priorities from DB for user + category
                      command = new SqlCommand("remove_user_query_in_category", con, trans);
                      command.CommandType = System.Data.CommandType.StoredProcedure;
                      command.Parameters.Add(new SqlParameter("@user_name", userName));
                      command.Parameters.Add(new SqlParameter("@c_name", categoryName));
                      command.ExecuteNonQuery();

                      command = new SqlCommand("remove_user_category", con, trans);
                      command.CommandType = System.Data.CommandType.StoredProcedure;
                      command.Parameters.Add(new SqlParameter("@user_name", userName));
                      command.Parameters.Add(new SqlParameter("@c_name", categoryName));
                      command.ExecuteNonQuery();

                      trans.Commit();
                  }
                  catch (Exception e)
                  {
                      Console.WriteLine("Could not remove category " + categoryName + " for user " + userName + " : " + e.Message);
                     
                      trans.Rollback();
                  }
              }
        }

        public void AddOrUpdateUserCategory(string userName, Category Category)
        {
            SqlTransaction trans = null;

                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    con.Open();
                    trans = con.BeginTransaction();
                    //Update Category settings
                    try
                    {
                        
                        SqlCommand command = new SqlCommand("add_or_update_user_category", con, trans);
                        command.Transaction = trans;
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.Parameters.Add(new SqlParameter("@user_name", userName));
                        command.Parameters.Add(new SqlParameter("@c_name", Category.Name));
                        command.Parameters.Add(new SqlParameter("@update_real_time", Category.UpdateRealTime));
                        command.ExecuteNonQuery();

                        //Update Times 

                        //Remove existing update times from DB for user + category
                        command = new SqlCommand("remove_user_category_notification_time", con, trans);
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.Parameters.Add(new SqlParameter("@user_name", userName));
                        command.Parameters.Add(new SqlParameter("@c_name", Category.Name));
                        command.ExecuteNonQuery();

                        command = new SqlCommand("add_category_notification_time", con, trans);
                        command.CommandType = System.Data.CommandType.StoredProcedure;

                        foreach (UpdateTime ut in Category.UpdateTimes)
                        {
                            command.Parameters.Clear();
                            command.Parameters.Add(new SqlParameter("@user_name", userName));
                            command.Parameters.Add(new SqlParameter("@c_name", Category.Name));
                            command.Parameters.Add(new SqlParameter("@day", (int)ut.Day));
                            command.Parameters.Add(new SqlParameter("@hour", (int)ut.Hour.TotalHours));
                            command.ExecuteNonQuery();

                        }

                        //Document Types

                        //Remove existing doc types from DB for user + category
                        command = new SqlCommand("remove_user_category_notify_doc_type", con, trans);
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.Parameters.Add(new SqlParameter("@user_name", userName));
                        command.Parameters.Add(new SqlParameter("@c_name", Category.Name));
                        command.ExecuteNonQuery();

                        command = new SqlCommand("add_category_notify_doc_type", con, trans);
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        foreach (Document.Type dt in Category.DocumentTypes)
                        {
                            command.Parameters.Clear();
                            command.Parameters.Add(new SqlParameter("@user_name", userName));
                            command.Parameters.Add(new SqlParameter("@c_name", Category.Name));
                            command.Parameters.Add(new SqlParameter("@dt_id", (int)dt));
                            command.ExecuteNonQuery();
                        }

                        //Category Priority

                        //Remove existing priorities from DB for user + category
                        command = new SqlCommand("remove_user_category_priority", con, trans);
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.Parameters.Add(new SqlParameter("@user_name", userName));
                        command.Parameters.Add(new SqlParameter("@c_name", Category.Name));
                        command.ExecuteNonQuery();

                        command = new SqlCommand("add_or_update_category_priority", con, trans);
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        foreach (DomainPriority dp in Category.DomainPriorities)
                        {
                            command.Parameters.Clear();
                            command.Parameters.Add(new SqlParameter("@user_name", userName));
                            command.Parameters.Add(new SqlParameter("@c_name", Category.Name));
                            command.Parameters.Add(new SqlParameter("@site_name", dp.SiteName));
                            command.Parameters.Add(new SqlParameter("@priority", dp.Priority));
                            command.ExecuteNonQuery();
                        }

                        //Add Queries for category.
                        //Remove existing priorities from DB for user + category
                        command = new SqlCommand("remove_user_query_in_category", con, trans);
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.Parameters.Add(new SqlParameter("@user_name", userName));
                        command.Parameters.Add(new SqlParameter("@c_name", Category.Name));
                        command.ExecuteNonQuery();

                        command = new SqlCommand("add_query_in_category", con, trans);
                        command.CommandType = System.Data.CommandType.StoredProcedure;

                        foreach (string query in Category.Queries)
                        {
                            command.Parameters.Clear();
                            command.Parameters.Add(new SqlParameter("@user_name", userName));
                            command.Parameters.Add(new SqlParameter("@c_name", Category.Name));
                            command.Parameters.Add(new SqlParameter("@query_text", query));
                            command.ExecuteNonQuery();


                        }
                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine("Could not update category " + Category.Name + " for user " + userName + " : " + e.Message);
                        trans.Rollback();
                    }
                }

        }


        public List<User> GetUsers()
        {
            List<User> Users = new List<User>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("get_user_names", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Users.Add(GetUserByUserName(reader["user_name"].ToString()));
                }
            }
            return Users;
        }

		public long GetMaxDocId()
		{
			using (SqlConnection con = new SqlConnection(connectionString))
			{
				con.Open();
				SqlCommand command = new SqlCommand("get_max_doc_id", con);
				command.CommandType = System.Data.CommandType.StoredProcedure;
				long maxID = (long)command.ExecuteScalar();
				return maxID;
			}
		}

		public long GetMinDocId()
		{
			using (SqlConnection con = new SqlConnection(connectionString))
			{
				con.Open();
				SqlCommand command = new SqlCommand("get_min_doc_id", con);
				command.CommandType = System.Data.CommandType.StoredProcedure;
				long minID = (long)command.ExecuteScalar();
				return minID;
			}
		}

        /// <summary>
        /// Inserts the results for a query by a certain user.
        /// The results are entered as rows in user_document , to be retrieved later by the 
        /// user application.
        /// </summary>
        /// <param name="User"></param>
        /// <param name="Query">query must already be in DB.</param>
        /// <param name="SearchResults">Documents returned by the query (with score!)</param>
        public void InsertUserQueryResults(User User, string Query, List<Document> SearchResults)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("add_user_query_result", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@user_name", User.UserName));
                command.Parameters.Add(new SqlParameter("@doc_id", 0));
                command.Parameters.Add(new SqlParameter("@query_text", Query));
                command.Parameters.Add(new SqlParameter("@score", System.Data.SqlDbType.Float));
                //Insert each result in order .
                foreach (Document doc in SearchResults)
                {
                    command.Parameters["@doc_id"].Value = doc.Id;
                    //Document isn't relevant
                    if (doc.Score == -1)
                        continue;
                    command.Parameters["@score"].Value = doc.Score;
                    command.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// Retrieves user's results by query, by order of insertion (dateTime)
        /// </summary>
       ///<returns>List of documents sorted by date, containing score for Query.</returns>
        public List<Document> GetUserSearchResultsByQuery(User User, string Query, bool IsRead)
        {
            List<Document> results = new List<Document>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("get_results_for_user_query_sorted_by_date", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@user_name", User.UserName));
                command.Parameters.Add(new SqlParameter("@query_text", Query));
                command.Parameters.Add(new SqlParameter("@is_read", IsRead));
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Document Doc = GetDocumentByID( long.Parse(reader["doc_id"].ToString()));
                    Doc.Score = (double)reader["score"];
                    results.Add(Doc);

                }
            }
            return results;
        }



        public List<string> GetQueries()
        {
            List<string> results = new List<string>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("get_all_queries", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    results.Add(reader[1].ToString());
                }
            }
            return results;
        }

        /// <summary>
        /// Gets all users who have this query defined.
        /// </summary>
        /// <param name="Query"></param>
        /// <returns></returns>
        public List<User> GetUsersForQuery(string Query)
        {
            List<User> results = new List<User>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("get_all_users_for_query", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@query_text", Query));
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    results.Add(GetUserByUserName(reader["user_name"].ToString()));
                }
            }
            return results;
        }


        /*
        public HybridDictionary GetDocumentsCollection(Word Word)
        {
            HybridDictionary ret = new HybridDictionary();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("get_documents_containing_word", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@word", Word.Text));
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    //Make document object.
                    Document Document = new Document(0, reader["doc_title"].ToString(), 
                         reader["doc_summary"].ToString(),  reader["doc_text"].ToString(),
                          reader["doc_author"].ToString(),  
                          System.DateTime.Parse(reader["doc_datetime"].ToString()),
                          reader["doc_url"].ToString(), 
                          Int32.Parse(reader["doc_comment_num"].ToString()),
                          (Document.Type)reader["doc_type"], 
                          Int32.Parse(reader["@doc_priority"].ToString()));
                    //Make List<WordPos>
                    command = new SqlCommand("get_word_positions_in_document", con);
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@word", Word.Text));
                    command.Parameters.Add(new SqlParameter("@doc_url", Document.Url));
                    List<WordPos> WordPosList = new List<WordPos>();
                    SqlDataReader InnerReader = command.ExecuteReader();
                    while (InnerReader.Read())
                    {
                        WordPos pos = new WordPos();
                        pos.Position = Int32.Parse(InnerReader["position"].ToString());
                        pos.Section = (Document.Section)Int32.Parse(InnerReader["ds_id"].ToString());
                        WordPosList.Add(pos);
                    }
                    
                    ret.Add(Document, WordPosList);
                       
                }
                
            }

            return ret;
        }

        */

        //TODO: make this generic.
        public List<Story> getStoriesContainingTerm(string term)
        {
            List<Story> lYnetStories = new List<Story>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("select * from document " +
                    "where doc_text like @doc_text",con);
                command.Parameters.Add(new SqlParameter("@doc_text", "%" + term + "%"));
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Story y = new Story();
                    y.Title = reader["doc_name"] as string;
                    y.Summary = reader["doc_summary"] as string;
                    y.Text = reader["doc_text"] as string;
                    lYnetStories.Add(y);
                }
            }
            return lYnetStories;
        }


        public void UpdateSites(List<Site> Sites)
        {
          
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("add_site", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@name", ""));
                command.Parameters.Add(new SqlParameter("@display_name", ""));
                command.Parameters.Add(new SqlParameter("@domain", ""));
                command.Parameters.Add(new SqlParameter("@story_name", ""));
                command.Parameters.Add(new SqlParameter("@forum_name", "פורומים"));
                command.Parameters.Add(new SqlParameter("@blog_name", "בלוגים"));
                command.Parameters.Add(new SqlParameter("@talkback_name", "בלוגים"));
                foreach (Site site in Sites)
                {
                    command.Parameters["@name"].Value = site.SiteNames.SiteName;
                    command.Parameters["@display_name"].Value = site.SiteNames.DisplayName;
                    command.Parameters["@domain"].Value = site.Domains[1];
                    command.Parameters["@story_name"].Value = site.SiteNames.StoryName;
                    command.Parameters["@talkback_name"].Value = site.SiteNames.TalkbackName;
                    command.Parameters["@forum_name"].Value = "פורומים";//site.SiteNames.ForumName;
                    command.Parameters["@blog_name"].Value = site.SiteNames.BlogName;
                    //command.Parameters.Add(new SqlParameter("@talkback_name", site.TalkbackRule.Name));
                    command.ExecuteNonQuery();
                }
            }
        }

        public List<string> GetAllSitesNames()
        {
            List<string> siteNames = new List<string>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("get_all_sites_names", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    siteNames.Add(reader["name"].ToString());
                }
            }
            var sorted = from d in siteNames
                         orderby
                         d ascending
                         select d;
            return new List<string>(sorted);
        }

        public SqlDataReader getMentionsByDate(string userName, string queryText, DateTime FromDate, DateTime ToDate)
        {
            Hashtable ht = new Hashtable();
          
                SqlConnection con = new SqlConnection(connectionString);
                con.Open();
                SqlCommand command = new SqlCommand("get_mentions_by_date", con);
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@user_name", userName));
                command.Parameters.Add(new SqlParameter("@query_text", queryText));
                command.Parameters.Add(new SqlParameter("@from_date", FromDate));
                command.Parameters.Add(new SqlParameter("@to_date", ToDate));
                SqlDataReader reader = command.ExecuteReader();
                return reader;
        }

        public bool IsNotXml(Uri uri)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("is_not_xml", con);
                command.Parameters.Add(new SqlParameter("@url", uri.ToString()));
                command.CommandType = System.Data.CommandType.StoredProcedure;
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    return true;
                }
            }
            return false;
        }

        public void SetUrlData(Uri uri, DateTime lastDownloadDate, bool isXml, string eTag)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("add_url_data", con);
                command.Parameters.Add(new SqlParameter("@url", uri.ToString()));
                if(!(lastDownloadDate == DateTime.MinValue))
                command.Parameters.Add(new SqlParameter("@last_download_date", lastDownloadDate));
                command.Parameters.Add(new SqlParameter("@is_xml",isXml));
                if (!(eTag == string.Empty))
                    command.Parameters.Add(new SqlParameter("@etag", eTag));
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.ExecuteNonQuery();
            }
        }

        public DateTime GetLastDownloadDate(Uri uri)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("get_last_download_date", con);
                command.Parameters.Add(new SqlParameter("@url", uri.ToString()));
                command.CommandType = System.Data.CommandType.StoredProcedure;
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    DateTime dateTime = new DateTime();
                   if (!DateTime.TryParse(reader["last_download_date"].ToString(), out dateTime))
                        return DateTime.MinValue;
                    else return dateTime;
                }
            }
            return DateTime.MinValue;
        }

        public string GetEtag(Uri uri)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("get_etag", con);
                command.Parameters.Add(new SqlParameter("@url", uri.ToString()));
                command.CommandType = System.Data.CommandType.StoredProcedure;
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    return reader["etag"].ToString();
                }
            }
            return string.Empty;
        }

        public void SetLastDownloadDate(Uri uri, DateTime lastDownloadDate)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand command = new SqlCommand("set_last_download_date", con);
                command.Parameters.Add(new SqlParameter("@url", uri.ToString()));
                command.Parameters.Add(new SqlParameter("@last_download_date", lastDownloadDate));
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.ExecuteNonQuery();
            }
        }
    }
}
